﻿using FinancialReportPowerPointAddIn.Helper;
using System;
using System.Drawing;
using System.Linq;
using PpChartType = Microsoft.Office.Core.XlChartType;
using Microsoft.EntityFrameworkCore;
using Microsoft.Office.Interop.PowerPoint;


namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateFourPartData
    {
        public static void Write(int year, int month, int comYear, int comMonth, string target)
        {
            Common.GetPLNameAndCode(target, out string flag, out string code);
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //开始写入数据
            using (var db = new FinancialReportContext())
            {
                //准备数据
                var actSales = db.PLDatas.Where(x => x.Year == year && x.Restaurant == flag && x.IsPlan == false).AsNoTracking().ToList();
                var planSales = db.PLDatas.Where(x => x.Year == year && x.Restaurant == flag && x.IsPlan == true).AsNoTracking().ToList();
                var comSales = db.PLDatas.Where(x => x.Year == comYear && x.Restaurant == flag && x.IsPlan == false).AsNoTracking().ToList();
                var ytdactSales = db.PLDatas.Where(x => x.Year == year && x.Month <= month && x.Restaurant == flag && x.IsPlan == false).AsNoTracking().ToList();
                var ytdplanSales = db.PLDatas.Where(x => x.Year == year && x.Month <= month && x.Restaurant == flag && x.IsPlan == true).AsNoTracking().ToList();
                var ytdcomSales = db.PLDatas.Where(x => x.Year == comYear && x.Month <= comMonth && x.Restaurant == flag && x.IsPlan == false).AsNoTracking().ToList();
                string vsplan = string.Empty;
                string vsytdplan = string.Empty;
                string vscom = string.Empty;
                string vsytdcom = string.Empty;
                string title = string.Empty;
                float left, top;
                #region 第一图表 TotalSales
                //写入平台差异图表                
                Chart chart = currentslide.Shapes.AddChart2(
                Style: 227,
                    Type: PpChartType.xlColumnClustered,
                    Left: 0,
                    Top: 0,
                    Width: currentslide.Master.Width / 2,
                    Height: currentslide.Master.Height / 2,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet = chart.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet.Cells[1, 1].Value = null;
                worksheet.Cells[2, 1].Value = "Act. Sales";
                worksheet.Cells[3, 1].Value = "Plan.Sales";
                worksheet.Cells[4, 1].Value = $"{comYear.ToString().Substring(2)}.Comps";
                for (int i = 1; i <= 12; i++)
                {
                    int col = i + 1;
                    var act = actSales.FirstOrDefault(x => x.Month == i);
                    var plan = planSales.FirstOrDefault(x => x.Month == i);
                    var com = comSales.FirstOrDefault(x => x.Month == i);
                    if (i == month)
                    {
                        var temp = Calculate.CalVar(act.营业净收入, plan.营业净收入);
                        vsplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = Calculate.CalVar(ytdactSales.Sum(x => x.营业净收入), ytdplanSales.Sum(x => x.营业净收入));
                        vsytdplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    if (i == comMonth)
                    {
                        var temp = Calculate.CalVar(act.营业净收入, com.营业净收入);
                        vscom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = Calculate.CalVar(ytdactSales.Sum(x => x.营业净收入), ytdcomSales.Sum(x => x.营业净收入));
                        vsytdcom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    worksheet.Cells[1, col].Value = i.ToString() + "月";
                    if (act != null)
                    {
                        worksheet.Cells[2, col].Value = (act.营业净收入 / 100000).ToString("n");
                    }
                    if (plan != null)
                    {
                        worksheet.Cells[3, col].Value = (plan.营业净收入 / 100000).ToString("n");
                    }
                    if (com != null && act != null)
                    {
                        worksheet.Cells[4, col].Value = Calculate.CalVar(act.营业净收入, com.营业净收入).ToString("p");
                    }
                }
                // 设置图表的数据范围            
                string rangeAddress = "='Sheet1'!" + worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[4, 13]].Address;
                chart.SetSourceData(rangeAddress, XlRowCol.xlRows);
                // 保存并关闭Excel工作簿
                chart.ChartData.Workbook.Close(true);
                chart.SeriesCollection(3).AxisGroup = XlAxisGroup.xlSecondary;
                chart.SeriesCollection(3).ChartType = PpChartType.xlLine;
                chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(238, 121, 66));
                chart.SeriesCollection(3).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(106, 90, 205));
                //设置主坐标轴系列重叠
                chart.ChartGroups(1).Overlap = 100;
                chart.SeriesCollection(1).ApplyDataLabels();
                Series series2 = chart.SeriesCollection(2);
                series2.Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
                series2.Border.LineStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSolid;
                series2.Format.Line.Weight = 1f;
                series2.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.Black);
                // 添加图表标题
                chart.HasTitle = true;
                title = flag == "总表" ? "Total" : flag;
                chart.ChartTitle.Text = $"{title}Sales";
                // 取消横向网格线
                chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 设置整个图表的字体大小
                chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
                // 设置图表标题字体大小
                chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
                chart.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                //添加备注                
                left = (currentslide.Master.Width / 2) - 200;
                top = 2;
                Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, left, top, 190, 15);
                titleBox.TextFrame.TextRange.Text = $"VS.PLAN：MTD {vsplan}\r\nVS.{comYear.ToString().Substring(2)}：MTD {vscom}\r\nVS.PLAN：YTD {vsytdplan}\r\nVS.{comYear.ToString().Substring(2)}：YTD {vsytdcom}";
                titleBox.TextFrame.TextRange.Font.Size = 11;
                titleBox.TextFrame.TextRange.Font.Color.RGB = ColorTranslator.ToOle(Color.Red);
                titleBox.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront);
                titleBox.TextFrame.TextRange.ParagraphFormat.Alignment = PpParagraphAlignment.ppAlignRight;
                #endregion
                #region 第二图表 Total薪资
                Chart chart2 = currentslide.Shapes.AddChart2(
                    Type: PpChartType.xlLineMarkers,
                    Left: currentslide.Master.Width / 2,
                    Top: 0,
                    Width: currentslide.Master.Width / 2,
                    Height: currentslide.Master.Height / 2,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet2 = chart2.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet2.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet2.Cells[1, 1].Value = null;
                worksheet2.Cells[2, 1].Value = "Act. 薪资";
                worksheet2.Cells[3, 1].Value = "Plan.薪资";
                worksheet2.Cells[4, 1].Value = $"{comYear.ToString().Substring(2)}.薪资";
                decimal max = 0, min = 0;
                for (int i = 1; i <= 12; i++)
                {
                    int col = i + 1;
                    var act = actSales.FirstOrDefault(x => x.Month == i);
                    var plan = planSales.FirstOrDefault(x => x.Month == i);
                    var com = comSales.FirstOrDefault(x => x.Month == i);
                    if (i == month)
                    {
                        var temp = act.薪资3项 / act.产品净收入 - plan.薪资3项 / plan.产品净收入;
                        vsplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.薪资3项) / ytdactSales.Sum(x => x.产品净收入) - ytdplanSales.Sum(x => x.薪资3项) / ytdplanSales.Sum(x => x.产品净收入);
                        vsytdplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    if (i == comMonth)
                    {
                        var temp = act.薪资3项 / act.产品净收入 - com.薪资3项 / com.产品净收入;
                        vscom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.薪资3项) / ytdactSales.Sum(x => x.产品净收入) - ytdcomSales.Sum(x => x.薪资3项) / ytdcomSales.Sum(x => x.产品净收入);
                        vsytdcom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    worksheet2.Cells[1, col].Value = i.ToString() + "月";
                    if (act != null)
                    {
                        max = Math.Max(act.薪资3项 / act.产品净收入, max);
                        min = Math.Min(act.薪资3项 / act.产品净收入, min);
                        if (i == 1)
                        {
                            min = act.薪资3项 / act.产品净收入;
                        }
                        worksheet2.Cells[2, col].Value = (act.薪资3项 / act.产品净收入).ToString("p");
                    }
                    if (plan != null)
                    {
                        max = Math.Max(plan.薪资3项 / plan.产品净收入, max);
                        min = Math.Min(plan.薪资3项 / plan.产品净收入, min);
                        worksheet2.Cells[3, col].Value = (plan.薪资3项 / plan.产品净收入).ToString("p");
                    }
                    if (com != null)
                    {
                        max = Math.Max(com.薪资3项 / com.产品净收入, max);
                        min = Math.Min(com.薪资3项 / com.产品净收入, min);
                        worksheet2.Cells[4, col].Value = (com.薪资3项 / com.产品净收入).ToString("p");
                    }
                }
                // 设置图表的数据范围            
                string rangeAddress2 = "='Sheet1'!" + worksheet2.Range[worksheet2.Cells[1, 1], worksheet2.Cells[4, 13]].Address;
                chart2.SetSourceData(rangeAddress2, XlRowCol.xlRows);
                // 保存并关闭Excel工作簿
                chart2.ChartData.Workbook.Close(true);
                // 添加图表标题
                chart2.HasTitle = true;
                chart2.ChartTitle.Text = $"{title}-薪资";
                // 添加图例
                chart2.HasLegend = true;
                chart2.SeriesCollection(1).ApplyDataLabels();
                chart2.SeriesCollection(2).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart2.SeriesCollection(3).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDot;
                chart2.SeriesCollection(1).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(0, 191, 255));
                chart2.SeriesCollection(2).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(102, 205, 0));
                chart2.SeriesCollection(3).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(255, 106, 106));
                // 设置Y轴的最大值和最小值
                Axis yAxis = chart2.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
                yAxis.MaximumScale = Convert.ToDouble(max + 0.02m); // 设置Y轴的最大值
                yAxis.MinimumScale = Convert.ToDouble(min - 0.02m); // 设置Y轴的最小值
                                                                    // 取消横向网格线
                chart2.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart2.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart2.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart2.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart2.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart2.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 设置整个图表的字体大小
                chart2.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
                // 设置图表标题字体大小
                chart2.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
                chart2.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                //添加备注                
                left = (currentslide.Master.Width) - 200;
                top = 2;
                titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, left, top, 190, 15);
                titleBox.TextFrame.TextRange.Text = $"VS.PLAN：MTD {vsplan}\r\nVS.{comYear.ToString().Substring(2)}：MTD {vscom}\r\nVS.PLAN：YTD {vsytdplan}\r\nVS.{comYear.ToString().Substring(2)}：YTD {vsytdcom}";
                titleBox.TextFrame.TextRange.Font.Size = 11;
                titleBox.TextFrame.TextRange.Font.Color.RGB = ColorTranslator.ToOle(Color.Red);
                titleBox.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront);
                titleBox.TextFrame.TextRange.ParagraphFormat.Alignment = PpParagraphAlignment.ppAlignRight;
                #endregion
                #region 第三图表 Total-营业成本
                Chart chart3 = currentslide.Shapes.AddChart2(
                    Type: PpChartType.xlLineMarkers,
                    Left: 0,
                    Top: currentslide.Master.Height / 2,
                    Width: currentslide.Master.Width / 2,
                    Height: currentslide.Master.Height / 2,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet3 = chart3.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet3.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet3.Cells[1, 1].Value = null;
                worksheet3.Cells[2, 1].Value = "Act. 营业成本";
                worksheet3.Cells[3, 1].Value = "Plan.营业成本";
                worksheet3.Cells[4, 1].Value = $"{comYear.ToString().Substring(2)}.营业成本";
                max = 0;
                min = 0;
                for (int i = 1; i <= 12; i++)
                {
                    int col = i + 1;
                    var act = actSales.FirstOrDefault(x => x.Month == i);
                    var plan = planSales.FirstOrDefault(x => x.Month == i);
                    var com = comSales.FirstOrDefault(x => x.Month == i);
                    if (i == month)
                    {
                        var temp = act.营业成本 / act.产品净收入 - plan.营业成本 / plan.产品净收入;
                        vsplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.营业成本) / ytdactSales.Sum(x => x.产品净收入) - ytdplanSales.Sum(x => x.营业成本) / ytdplanSales.Sum(x => x.产品净收入);
                        vsytdplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    if (i == comMonth)
                    {
                        var temp = act.营业成本 / act.产品净收入 - com.营业成本 / com.产品净收入;
                        vscom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.营业成本) / ytdactSales.Sum(x => x.产品净收入) - ytdcomSales.Sum(x => x.营业成本) / ytdcomSales.Sum(x => x.产品净收入);
                        vsytdcom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    worksheet3.Cells[1, col].Value = i.ToString() + "月";
                    if (act != null)
                    {
                        max = Math.Max(act.营业成本 / act.产品净收入, max);
                        min = Math.Min(act.营业成本 / act.产品净收入, min);
                        if (i == 1)
                        {
                            min = act.营业成本 / act.产品净收入;
                        }
                        worksheet3.Cells[2, col].Value = (act.营业成本 / act.产品净收入).ToString("p");
                    }
                    if (plan != null)
                    {
                        max = Math.Max(plan.营业成本 / plan.产品净收入, max);
                        min = Math.Min(plan.营业成本 / plan.产品净收入, min);
                        worksheet3.Cells[3, col].Value = (plan.营业成本 / plan.产品净收入).ToString("p");
                    }
                    if (com != null)
                    {
                        max = Math.Max(com.营业成本 / com.产品净收入, max);
                        min = Math.Min(com.营业成本 / com.产品净收入, min);
                        worksheet3.Cells[4, col].Value = (com.营业成本 / com.产品净收入).ToString("p");
                    }
                }
                // 设置图表的数据范围            
                string rangeAddress3 = "='Sheet1'!" + worksheet3.Range[worksheet3.Cells[1, 1], worksheet3.Cells[4, 13]].Address;
                chart3.SetSourceData(rangeAddress3, XlRowCol.xlRows);
                // 保存并关闭Excel工作簿
                chart3.ChartData.Workbook.Close(true);
                // 添加图表标题
                chart3.HasTitle = true;
                chart3.ChartTitle.Text = $"{title}-营业成本";
                // 添加图例
                chart3.HasLegend = true;
                chart3.SeriesCollection(1).ApplyDataLabels();
                chart3.SeriesCollection(2).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart3.SeriesCollection(3).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDot;
                chart3.SeriesCollection(1).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(0, 191, 255));
                chart3.SeriesCollection(2).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(102, 205, 0));
                chart3.SeriesCollection(3).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(255, 106, 106));
                // 设置Y轴的最大值和最小值
                var yAxis3 = chart3.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
                yAxis3.MaximumScale = Convert.ToDouble(max + 0.01m); // 设置Y轴的最大值
                yAxis3.MinimumScale = Convert.ToDouble(min - 0.01m); // 设置Y轴的最小值
                                                                     // 取消横向网格线
                chart3.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart3.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart3.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart3.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart3.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart3.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 设置整个图表的字体大小
                chart3.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
                // 设置图表标题字体大小
                chart3.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
                chart3.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                //添加备注                
                left = (currentslide.Master.Width / 2) - 200;
                top = (currentslide.Master.Height / 2) + 2;
                titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, left, top, 190, 15);
                titleBox.TextFrame.TextRange.Text = $"VS.PLAN：MTD {vsplan}\r\nVS.{comYear.ToString().Substring(2)}：MTD {vscom}\r\nVS.PLAN：YTD {vsytdplan}\r\nVS.{comYear.ToString().Substring(2)}：YTD {vsytdcom}";
                titleBox.TextFrame.TextRange.Font.Size = 11;
                titleBox.TextFrame.TextRange.Font.Color.RGB = ColorTranslator.ToOle(Color.Red);
                titleBox.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront);
                titleBox.TextFrame.TextRange.ParagraphFormat.Alignment = PpParagraphAlignment.ppAlignRight;
                #endregion
                #region 第四图表 Total-PAC
                Chart chart4 = currentslide.Shapes.AddChart2(
                    Type: PpChartType.xlLineMarkers,
                    Left: currentslide.Master.Width / 2,
                    Top: currentslide.Master.Height / 2,
                    Width: currentslide.Master.Width / 2,
                    Height: currentslide.Master.Height / 2,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet4 = chart4.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet4.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet4.Cells[1, 1].Value = null;
                worksheet4.Cells[2, 1].Value = "Act. PAC";
                worksheet4.Cells[3, 1].Value = "Plan.PAC";
                worksheet4.Cells[4, 1].Value = $"{comYear.ToString().Substring(2)}.PAC";
                max = 0;
                min = 0;
                for (int i = 1; i <= 12; i++)
                {
                    int col = i + 1;
                    var act = actSales.FirstOrDefault(x => x.Month == i);
                    var plan = planSales.FirstOrDefault(x => x.Month == i);
                    var com = comSales.FirstOrDefault(x => x.Month == i);
                    if (i == month)
                    {
                        var temp = act.可控利润 / act.产品净收入 - plan.可控利润 / plan.产品净收入;
                        vsplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.可控利润) / ytdactSales.Sum(x => x.产品净收入) - ytdplanSales.Sum(x => x.可控利润) / ytdplanSales.Sum(x => x.产品净收入);
                        vsytdplan = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    if (i == comMonth)
                    {
                        var temp = act.可控利润 / act.产品净收入 - com.可控利润 / com.产品净收入;
                        vscom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                        temp = ytdactSales.Sum(x => x.可控利润) / ytdactSales.Sum(x => x.产品净收入) - ytdcomSales.Sum(x => x.可控利润) / ytdcomSales.Sum(x => x.产品净收入);
                        vsytdcom = temp > 0 ? "+" + temp.ToString("p") : temp.ToString("p");
                    }
                    worksheet4.Cells[1, col].Value = i.ToString() + "月";
                    if (act != null)
                    {
                        max = Math.Max(act.可控利润 / act.产品净收入, max);
                        min = Math.Min(act.可控利润 / act.产品净收入, min);
                        if (i == 1)
                        {
                            min = act.可控利润 / act.产品净收入;
                        }
                        worksheet4.Cells[2, col].Value = (act.可控利润 / act.产品净收入).ToString("p");
                    }
                    if (plan != null)
                    {
                        max = Math.Max(plan.可控利润 / plan.产品净收入, max);
                        min = Math.Min(plan.可控利润 / plan.产品净收入, min);
                        worksheet4.Cells[3, col].Value = (plan.可控利润 / plan.产品净收入).ToString("p");
                    }
                    if (com != null)
                    {
                        max = Math.Max(com.可控利润 / com.产品净收入, max);
                        min = Math.Min(com.可控利润 / com.产品净收入, min);
                        worksheet4.Cells[4, col].Value = (com.可控利润 / com.产品净收入).ToString("p");
                    }
                }
                // 设置图表的数据范围            
                string rangeAddress4 = "='Sheet1'!" + worksheet4.Range[worksheet4.Cells[1, 1], worksheet4.Cells[4, 13]].Address;
                chart4.SetSourceData(rangeAddress4, XlRowCol.xlRows);
                // 保存并关闭Excel工作簿
                chart4.ChartData.Workbook.Close(true);
                // 添加图表标题
                chart4.HasTitle = true;
                chart4.ChartTitle.Text = $"{title}-PAC";
                // 添加图例
                chart4.HasLegend = true;
                chart4.SeriesCollection(1).ApplyDataLabels();
                chart4.SeriesCollection(2).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDash;
                chart4.SeriesCollection(3).Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSysDot;
                chart4.SeriesCollection(1).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(0, 191, 255));
                chart4.SeriesCollection(2).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(102, 205, 0));
                chart4.SeriesCollection(3).Format.Line.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(255, 106, 106));
                // 设置Y轴的最大值和最小值
                var yAxis4 = chart4.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
                yAxis4.MaximumScale = Convert.ToDouble(max + 0.01m); // 设置Y轴的最大值
                yAxis4.MinimumScale = Convert.ToDouble(min - 0.01m); // 设置Y轴的最小值
                                                                     // 取消横向网格线
                chart4.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart4.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart4.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart4.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart4.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart4.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 设置整个图表的字体大小
                chart4.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
                // 设置图表标题字体大小
                chart4.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
                chart4.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                //添加备注                
                left = currentslide.Master.Width - 200;
                top = (currentslide.Master.Height / 2) + 2;
                titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, left, top, 190, 15);
                titleBox.TextFrame.TextRange.Text = $"VS.PLAN：MTD {vsplan}\r\nVS.{comYear.ToString().Substring(2)}：MTD {vscom}\r\nVS.PLAN：YTD {vsytdplan}\r\nVS.{comYear.ToString().Substring(2)}：YTD {vsytdcom}";
                titleBox.TextFrame.TextRange.Font.Size = 11;
                titleBox.TextFrame.TextRange.Font.Color.RGB = ColorTranslator.ToOle(Color.Red);
                titleBox.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront);
                titleBox.TextFrame.TextRange.ParagraphFormat.Alignment = PpParagraphAlignment.ppAlignRight;
                #endregion
                //添加分割线
                var line1 = currentslide.Shapes.AddLine(0, currentslide.Master.Height / 2, currentslide.Master.Width, currentslide.Master.Height / 2).Line;
                var line2 = currentslide.Shapes.AddLine(currentslide.Master.Width / 2, 0, currentslide.Master.Width / 2, currentslide.Master.Height).Line;
                line1.ForeColor.RGB = ColorTranslator.ToOle(Color.Black);
                line2.ForeColor.RGB = ColorTranslator.ToOle(Color.Black);
            }
        }
    }
}
